﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for UserBO
    /// </summary>
    public class UserBO : DBConnectionBO
    {
        public UserBO()
            : base()
        {

        }

        /// <summary>
        /// Get list user
        /// </summary>
        /// <returns></returns>
        public List<User> GetListUser()
        {
            var listUser = new List<User>();
            const string query = "SELECT TOP 100 * FROM [PrivateBlog].[dbo].[Users]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var user = new User
                    {
                        Id = (int)dataTable.Rows[i][0],
                        IdUserRole = (int)dataTable.Rows[i][1],
                        UserName = (string)dataTable.Rows[i][2],
                        FullName = (string)dataTable.Rows[i][3],
                        Avatar = (string)dataTable.Rows[i][4],
                        Password = (string)dataTable.Rows[i][5],
                        HashPassword = (string)dataTable.Rows[i][6],
                        TheNewPassword = (string)dataTable.Rows[i][7],
                        TheLastPassword = (string)dataTable.Rows[i][8],
                        Gender = (int)dataTable.Rows[i][9],
                        Birthday = (DateTime)dataTable.Rows[i][10],
                        School = (string)dataTable.Rows[i][11],
                        Working = (string)dataTable.Rows[i][12],
                        Company = (string)dataTable.Rows[i][13],
                        Language = (string)dataTable.Rows[i][14],
                        Sport = (string)dataTable.Rows[i][15],
                        Intro = (string)dataTable.Rows[i][16],
                        Point = (int)dataTable.Rows[i][17],
                        LastDateUpdated = (DateTime)dataTable.Rows[i][18],
                        LastUpdatedBy = (string)dataTable.Rows[i][19],
                        Status = Convert.ToInt32(dataTable.Rows[i][20])
                    };
                listUser.Add(user);
            }
            return listUser;
        }

        /// <summary>
        /// Add a new user
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddUser(User user)
        {
            var query =
                "INSERT INTO [PrivateBlog].[dbo].[Users]([IdUserRole],[UserName],[Avatar],[Password]," +
                "[HashPassword],[TheNewPassword],[TheLastPassword],[Gender],[Birthday],[School],[Working]," +
                "[Company],[Language],[Sport],[Intro],[LastDateUpdated],[LastUpdatedBy],[Status]) VALUES ( "
                + user.IdUserRole + ", '" +
                user.UserName + "', '" +
                user.Avatar + "', '" +
                user.Password + "', '" +
                user.HashPassword + "', '" +
                user.TheNewPassword + "', '" +
                user.TheLastPassword + "', " +
                user.Gender + ", " + user.Birthday + ", N'" +
                user.School + "', N'" + user.Working + "', N'" +
                user.Company + "', N'" + user.Language + "', N'" +
                user.Sport + "', N'" + user.Intro + "', " + "GETDATE(), N'" +
                user.LastUpdatedBy + "', " + user.Status;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit a user
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int EditUser(User user)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[Users]" +
                        "SET IdUserRole = N'" + user.IdUserRole + "', " +
                        "UserName = N'" + user.UserName + "', " +
                        "Avatar = N'" + user.Avatar + "', " +
                        "Password = '" + user.Password + "', " +
                        "HashPassword = '" + user.HashPassword + "', " +
                        "TheNewPassword = '" + user.TheNewPassword + "', " +
                        "Gender = " + user.Gender + ", " +
                        "Birthday = " + user.Birthday + ", " +
                        "School = N'" + user.School + "', " +
                        "Working = N'" + user.Working + "', " +
                        "Company = N'" + user.Company + "', " +
                        "Language = N'" + user.Language + "', " +
                        "Sport = N'" + user.Sport + "', " +
                        "Intro = N'" + user.Intro + "', " +
                        "LastDateUpdated = GETDATE(), " +
                        "LastUpdatedBy = N'" + user.LastUpdatedBy + "', " +
                        "Status = " + user.Status + ", " +
                        "WHERE Id = " + user.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete a user
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int DeleteUser(User user)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[Users] " +
                        " WHERE Id = " + user.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Get info of user through id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<User> GetInfoUser(int id)
        {
            var listUser = new List<User>();
            var query = "SELECT * FROM [PrivateBlog].[dbo].[Users] WHERE Id = " + id;
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var user = new User();

                user.Id = (int) dataTable.Rows[i][0];
                user.IdUserRole = (int) dataTable.Rows[i][1];
                user.UserName = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
                user.FullName = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                user.Avatar = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                user.Password = (dataTable.Rows[i][5] == DBNull.Value) ? string.Empty : dataTable.Rows[i][5].ToString();
                user.HashPassword = (dataTable.Rows[i][6] == DBNull.Value) ? string.Empty : dataTable.Rows[i][6].ToString();
                user.TheNewPassword = (dataTable.Rows[i][7] == DBNull.Value) ? string.Empty : dataTable.Rows[i][7].ToString();
                user.TheLastPassword = (dataTable.Rows[i][8] == DBNull.Value) ? string.Empty : dataTable.Rows[i][8].ToString();
                user.Gender = Convert.ToInt32(dataTable.Rows[i][9]);
                user.Birthday = (DateTime)dataTable.Rows[i][10];
                user.School = (dataTable.Rows[i][11] == DBNull.Value) ? string.Empty : dataTable.Rows[i][11].ToString();
                user.Working = (dataTable.Rows[i][12] == DBNull.Value) ? string.Empty : dataTable.Rows[i][12].ToString();
                user.Company = (dataTable.Rows[i][13] == DBNull.Value) ? string.Empty : dataTable.Rows[i][13].ToString();
                user.Language = (dataTable.Rows[i][14] == DBNull.Value) ? string.Empty : dataTable.Rows[i][14].ToString();
                user.Sport = (dataTable.Rows[i][15] == DBNull.Value) ? string.Empty : dataTable.Rows[i][15].ToString();
                user.Intro = (dataTable.Rows[i][16] == DBNull.Value) ? string.Empty : dataTable.Rows[i][16].ToString();
                user.Point = (dataTable.Rows[i][17] == DBNull.Value) ? 0 : (int)dataTable.Rows[i][17];
                user.LastDateUpdated = (dataTable.Rows[i][18] == DBNull.Value) ? DateTime.Now : (DateTime)dataTable.Rows[i][18];
                user.LastUpdatedBy = (dataTable.Rows[i][19] == DBNull.Value) ? string.Empty : dataTable.Rows[i][19].ToString();
                user.Status = (dataTable.Rows[i][20] == DBNull.Value ? 0 : Convert.ToInt32(dataTable.Rows[i][20]));

                listUser.Add(user);
            }
            return listUser;
        }

        /// <summary>
        /// Check exists user
        /// </summary>
        /// <returns></returns>
        public List<User> CheckExistsUser(User _user)
        {
            var listUser = new List<User>();
            var query = "SELECT TOP 100 * FROM [PrivateBlog].[dbo].[Users] WHERE UserName like '" + _user.UserName + "' and Password like '" + _user.Password + "'";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var user = new User();

                user.Id = (int) dataTable.Rows[i][0];
                user.IdUserRole = (int) dataTable.Rows[i][1];
                user.UserName = (string) dataTable.Rows[i][2];
                user.FullName = (dataTable.Rows[i][3] == DBNull.Value) ? string.Empty : dataTable.Rows[i][3].ToString();
                user.Avatar = (dataTable.Rows[i][4] == DBNull.Value) ? string.Empty : dataTable.Rows[i][4].ToString();
                user.Password = (string) dataTable.Rows[i][5];
                user.HashPassword = (dataTable.Rows[i][6] == DBNull.Value)
                                        ? string.Empty
                                        : dataTable.Rows[i][6].ToString();
                user.TheNewPassword = (dataTable.Rows[i][7] == DBNull.Value)
                                          ? string.Empty
                                          : dataTable.Rows[i][7].ToString();
                user.TheLastPassword = (dataTable.Rows[i][8] == DBNull.Value)
                                           ? string.Empty
                                           : dataTable.Rows[i][8].ToString();
                user.Gender = (dataTable.Rows[i][9] == DBNull.Value) ? 0 : Convert.ToInt32(dataTable.Rows[i][9]);
                user.Birthday = (DateTime) dataTable.Rows[i][10];
                user.School = (dataTable.Rows[i][11] == DBNull.Value) ? string.Empty : dataTable.Rows[i][11].ToString();
                user.Working = (dataTable.Rows[i][12] == DBNull.Value) ? string.Empty : dataTable.Rows[i][12].ToString();
                user.Company = (dataTable.Rows[i][13] == DBNull.Value) ? string.Empty : dataTable.Rows[i][13].ToString();
                user.Language = (dataTable.Rows[i][14] == DBNull.Value)
                                    ? string.Empty
                                    : dataTable.Rows[i][14].ToString();
                user.Sport = (dataTable.Rows[i][15] == DBNull.Value) ? string.Empty : dataTable.Rows[i][15].ToString();
                user.Intro = (dataTable.Rows[i][16] == DBNull.Value) ? string.Empty : dataTable.Rows[i][16].ToString();
                user.Point = (dataTable.Rows[i][17] == DBNull.Value) ? 0 : (int) dataTable.Rows[i][17];
                user.LastDateUpdated = (DateTime) dataTable.Rows[i][18];
                user.LastUpdatedBy = (dataTable.Rows[i][19] == DBNull.Value)
                                         ? string.Empty
                                         : dataTable.Rows[i][19].ToString();
                user.Status = Convert.ToInt32(dataTable.Rows[i][20]);
            
                listUser.Add(user);
            }
            return listUser;
        }

    }
}